﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.IO;
using System.Timers;


namespace RegisterClientForm
{
    public class SyncApp
    {
        string connectionString = "Data Source=NHAT-PC;Initial Catalog=ClientDB;Integrated Security=True";
       
        public string SyncFromClientToServer()
        {
        //Convert dataset to xml string and return string
            string xmlstring="";
            SqlConnection cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand("select * from UserInfo where Flag=0", cnn);
   
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            sda.SelectCommand = cmd;
            DataSet ds = new DataSet("DataSet");
            sda.Fill(ds,"UserInfo");

            //Create xml file
            MemoryStream ms = new MemoryStream();
            ds.WriteXml(ms);
            StreamReader sr = new StreamReader(ms, System.Text.Encoding.UTF8);
            ms.Position = 0;
            xmlstring = sr.ReadToEnd();
                
            sr.Close();
            ms.Close();

            return xmlstring;          
        }
  //========================================================================================//
  //========================================================================================//
        public void SyncFromServerToClient(string xmlstring)
        {
        //Conver xmlstring to dataset
            if (xmlstring != "")
            {
                StringReader strReader = null;
                XmlTextReader xmltxtReader = null;
                DataSet ds;
                try
                {

                    ds = new DataSet();
                    strReader = new StringReader(xmlstring);

                    //load xmltxtReader from strReader
                    xmltxtReader = new XmlTextReader(strReader);
                    ds.ReadXml(xmltxtReader);
                }
                finally
                {
                    if (xmltxtReader == null) xmltxtReader.Close();
                }
                //===============================================
                // sync client
                
                insert_dataset(ds);
            }
            
        }

        public void insert_dataset(DataSet ds)
        {

            int col = ds.Tables["UserInfo"].Columns.Count;
            int row = ds.Tables["UserInfo"].Rows.Count;
            string name = ds.Tables["UserInfo"].TableName;
            string fields = "";

            for (int i = 0; i < ds.Tables[name].Columns.Count; i++)
            {
                if(i == 0)
                    fields += ds.Tables[name].Columns[i].ColumnName;
                else fields += "," + ds.Tables[name].Columns[i].ColumnName;
            }
            string[] str_fields = fields.Split(',');
            ArrayList arr_rec = new ArrayList();

            for (int i = 0; i < row; i++)
            {
                for (int j = 0; j < col; j++)
                {
                    arr_rec.Add(ds.Tables[name].Rows[i].ItemArray[j]);
                }
            }
            //insert table into database
            insert_table(arr_rec, name, str_fields);
            arr_rec.Clear();
        }

        public void insert_table(ArrayList arr_data, string tableName, string[] fields)
        {
            string str_command, str_param;
            string fieldtable = string.Join(",", fields);

            string[] fieldsSelectarr = new string[fields.Length];
            for (int i = 0; i < fields.Length; i++)
            {
                fieldsSelectarr[i] = fields[i]+ " = @" + fields[i];
            }

            string fieldsSelect = string.Join(",", fieldsSelectarr);

            for (int i = 0; i < fields.Length; i++)
            {
                fields[i] = "@" + fields[i];
            }
            str_param = string.Join(",", fields);
            str_command = "insert into " + tableName + "(" + fieldtable + ") values(" + str_param + ")";

           // str_command = "insert into " + tableName + "(" + fieldtable + ") select " + str_param +" where not exists (select * from "+ tableName + " where " + fieldsSelect + ")" ;

            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            int row = arr_data.Count / fields.Length;
            int pos=0;
            for(int j = 0; j < row; j++)
            {
                SqlCommand cmd = new SqlCommand(str_command, conn);
                int k = 0;
                for (int i = pos; i < arr_data.Count; i++)
                {
                    cmd.Parameters.AddWithValue(fields[k], arr_data[i]);
                    k++;
                    pos++;
                    if (k == fields.Length)
                    {
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            
            conn.Close();
        }
        //===================================================================================//
        public void fromClientToServer()
        {
            Service.RegisterService db = new Service.RegisterService();
            string xml = SyncFromClientToServer();
            db.getXmlString(xml);
        }
        public void fromServerToClient()
        {
            Service.RegisterService db = new Service.RegisterService();
            string xml = db.createXmlString();
            SyncFromServerToClient(xml);
        }
        //===================================================================================//
    }
}
